
/* 
  Nuevo mecanismo de drop de vistas. 
  A partir de este script usar abaco_utiles.aaVistasDropear() y abaco_utiles.aaVistasRestaurar() cada 
  vez que se modifiquen columnas.
  
  ric 
  
  Ampliar columnas pad.email(2000) y pad.mailsitio(300)
  ale - 27/09/2022
  
 */
 
 -- SCHEMA: abaco_utiles

-- DROP SCHEMA abaco_utiles ;

DROP FUNCTION IF EXISTS public.aaDropVistas();
DROP FUNCTION IF EXISTS public.aaCreateVistas();

CREATE SCHEMA abaco_utiles
    AUTHORIZATION abacouser;
	
	
CREATE OR REPLACE FUNCTION abaco_utiles.aavistasdropear(
	)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
  cursor_vistas REFCURSOR;
  vEsquema varchar;
  vVista   varchar;
  vSQL     varchar;
  
BEGIN
  IF (SELECT COUNT(*) FROM pg_views WHERE schemaname not in ('pg_catalog','information_schema')) = 0 THEN 
     RAISE EXCEPTION 'ERROR GRAVE: No hay vistas. Puede ser que haya ejecutado un script incorrectamente, debe continuar la migracion en forma manual';
  END IF;

  DROP TABLE IF EXISTS abaco_utiles.vistas;
  CREATE TABLE abaco_utiles.vistas (orden integer, esquema varchar, vista varchar, sql varchar);
  INSERT INTO abaco_utiles.vistas SELECT 0, schemaname, viewname, definition from pg_views WHERE schemaname not in ('pg_catalog','information_schema');

  OPEN cursor_vistas FOR
       SELECT esquema, vista, sql
       FROM abaco_utiles.vistas;

  FETCH cursor_vistas INTO vEsquema, vVista, vSQL;
  LOOP
    EXIT WHEN (NOT FOUND);
   EXECUTE 'DROP VIEW IF EXISTS ' || vEsquema || '.' || vVista || ' CASCADE';
    FETCH cursor_vistas INTO vEsquema, vVista, vSQL;
  END LOOP;
  RETURN True;
END;
$BODY$;

ALTER FUNCTION abaco_utiles.aavistasdropear()
    OWNER TO abacouser;


CREATE OR REPLACE FUNCTION abaco_utiles.aavistasgenerardependientes(
	)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
  vEsquema_usa varchar;
  vVista_usa   varchar;
  vVistas      refcursor;
BEGIN
  /* Genera la tabla de dependencia de vistas en base a la tabla vistas y sus sql
     la tabla vistas la genera la función aaDropVistas antes de dropearlas
	 Usa la función aaVistasPreproceso 
  */

  DROP TABLE IF EXISTS abaco_utiles.vistas_dependientes;
  
  CREATE TABLE abaco_utiles.vistas_dependientes
  (esquema_usa varchar, vista_usa varchar, esquema_usada varchar, vista_usada varchar);

  OPEN vVistas FOR SELECT esquema, vista FROM abaco_utiles.vistas;
  
  FETCH vVistas INTO vEsquema_usa, vVista_usa;
  LOOP 
    IF NOT FOUND THEN EXIT; END IF;
	
    INSERT INTO abaco_utiles.vistas_dependientes
	SELECT vEsquema_usa, vVista_usa, usadas.esquema_vistas, usadas.vista_vistas
	FROM (SELECT * FROM abaco_utiles.aaVistasPreprocesar(vEsquema_usa, vVista_usa)) AS usadas
	group by vEsquema_usa, vVista_usa, usadas.esquema_vistas, usadas.vista_vistas;
  
    FETCH vVistas INTO vEsquema_usa, vVista_usa;
  END LOOP;		
  
  DELETE FROM abaco_utiles.vistas_dependientes 
  WHERE NOT EXISTS (SELECT * FROM abaco_utiles.vistas WHERE esquema = esquema_usada and vista = vista_usada);

  CLOSE vVistas;

  RETURN TRUE;
END;
$BODY$;

ALTER FUNCTION abaco_utiles.aavistasgenerardependientes()
    OWNER TO abacouser;

-- FUNCTION: abaco_utiles.aavistasordenar()

-- DROP FUNCTION abaco_utiles.aavistasordenar();

CREATE OR REPLACE FUNCTION abaco_utiles.aavistasordenar(
	)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
  vEsquema	varchar;
  vVista	varchar;
  vEsquema_usada	varchar;
  vVista_usada		varchar;
  vOrden	integer;
  vVistas   refcursor;
BEGIN
  /* Define el orden de ejecución de las vistas en base a la tabla vistasDependientes */

--  (esquema_usa varchar, vista_usa varchar, esquema_usada varchar, vista_usada varchar);

  UPDATE abaco_utiles.vistas SET orden = 1000;

  OPEN vVistas FOR SELECT v.esquema, v.vista, vd.esquema_usada, vd.vista_usada 
  FROM abaco_utiles.vistas v
  INNER JOIN abaco_utiles.vistas_dependientes vd on v.esquema = vd.esquema_usa and v.vista = vd.vista_usa
  ORDER BY v.esquema, v.vista, vd.esquema_usada, vd.vista_usada;
  
  FETCH vVistas INTO vEsquema, vVista, vEsquema_usada, vVista_usada;
    
  LOOP 
    IF NOT FOUND THEN EXIT; END IF;
    /* orden de la que usa al resto, es la base para calificar a sus hijas con un valor 10 puntos menor 
	   el orden puede haber cambiado desde que se abrió el cursor
	*/
	SELECT orden INTO vOrden
    FROM abaco_utiles.vistas
    WHERE esquema = vEsquema and vista = vVista;
	
	/* Se procesa cada una de sus hijas directas */	
	PERFORM (SELECT abaco_utiles.aaVistasOrdenarUsadas(vEsquema_usada, vVista_usada, vOrden));
  
    FETCH vVistas INTO vEsquema_usada, vVista_usada;
  END LOOP;		

  CLOSE vVistas;

  RETURN TRUE;
END;
$BODY$;

ALTER FUNCTION abaco_utiles.aavistasordenar()
    OWNER TO abacouser;

-- FUNCTION: abaco_utiles.aavistasordenarusadas(character varying, character varying, integer)

-- DROP FUNCTION abaco_utiles.aavistasordenarusadas(character varying, character varying, integer);

CREATE OR REPLACE FUNCTION abaco_utiles.aavistasordenarusadas(
	pesquema character varying,
	pvista character varying,
	porden integer)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
  vEsquema_usada	varchar;
  vVista_usada		varchar;
  vOrden	integer;
  vVistas   refcursor;
BEGIN
  /* Define el orden de ejecución de las hijas de una vista determinada, es recursivo con sus hijas */
  
  /* actualizo el orden de la usada en -10 puntos de quien la usa, si es que no tiene ya un valor menor */
  UPDATE abaco_utiles.vistas SET orden = pOrden - 10 
  WHERE esquema = pEsquema and vista = pVista
  AND orden > pOrden - 10;
  
  /* leo el orden resultante */
  SELECT orden INTO vOrden 
  FROM abaco_utiles.vistas 
  WHERE esquema = pEsquema and vista = pVista;
  
  OPEN vVistas FOR SELECT vd.esquema_usada, vd.vista_usada 
  FROM abaco_utiles.vistas_dependientes vd 
  WHERE vd.esquema_usa = pEsquema and vd.vista_usa = pVista
  ORDER BY esquema_usada, vista_usada;

  FETCH vVistas INTO vEsquema_usada, vVista_usada;

  LOOP 
    IF NOT FOUND THEN EXIT; END IF;
	  
	PERFORM (SELECT abaco_utiles.aaVistasOrdenarUsadas(vEsquema_usada, vVista_usada, vOrden));
	
    FETCH vVistas INTO vEsquema_usada, vVista_usada;
  END LOOP;		

  CLOSE vVistas;

  RETURN TRUE;
END;
$BODY$;

ALTER FUNCTION abaco_utiles.aavistasordenarusadas(character varying, character varying, integer)
    OWNER TO abacouser;
	
	
	
CREATE OR REPLACE FUNCTION abaco_utiles.aavistaspreprocesar(
	esquema_base character varying,
	vista_base character varying)
    RETURNS TABLE(esquema_vistas character varying, vista_vistas character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE
  vEsquema varchar;
  vVista   varchar;
  vSQL     varchar;
  vCandidata 		varchar;
  vEsquemaCandidato varchar;
  vProximoFrom		varchar;
  vProximoToken     integer;
  vVistas           refcursor;
BEGIN
  DROP TABLE IF EXISTS vistas_usadas;
  CREATE TEMP TABLE vistas_usadas (esquema_vistas varchar, vista_vistas varchar) 
  ON COMMIT DROP;
  
  SELECT esquema, vista, sql INTO vEsquema, vVista, vSql 
  FROM abaco_utiles.vistas
  WHERE vista = vista_base and esquema = esquema_base;
  
  vSql := replace(replace(vSql, '(', ' '),')',' ');
  vSql := replace(vSql, chr(34), ' ');
  vSql := replace(vSql, chr(10), ' ');
  vSql := replace(vSql, chr(13), ' ');
  vSql := replace(vSql, '  ', ' ');
  vSql := replace(vSql, '  ', ' ');
  vSql := replace(vSql, '  ', ' ');
  vSql := replace(vSql, '  ', ' ');
  
  vCandidata   := coalesce(vSql,'');
  vProximoFrom := vCandidata;
  -- Proceso FROM
  LOOP 
     IF position('FROM' IN vCandidata) = 0 THEN EXIT; END IF;
	 
	 vProximoFrom := substring(vCandidata, POSITION('FROM' IN vCandidata)+5,length(vCandidata));
	 vCandidata   := vProximoFrom;
	 vProximoToken := length(vCandidata);
	 if position(' ' IN vCandidata) > 0 AND position(' ' IN vCandidata) < vProximoToken THEN  
	    vProximoToken := position(' ' IN vCandidata);
	 END IF;
	 if position(';' IN vCandidata) > 0 AND position(';' IN vCandidata) < vProximoToken THEN  
	    vProximoToken := position(';' IN vCandidata);
	 END IF;

	 vCandidata := trim(substring(vCandidata,1, vProximoToken - 1));
	 
	 if vCandidata ILIKE 'SELECT%' THEN vCandidata := ''; END IF;
	 
	 IF vCandidata <> '' THEN 
	 	IF position('.' in vCandidata) > 0 THEN 
	    	vEsquemaCandidato := substring(vCandidata, 1, position('.' in vCandidata) - 1);
	   		vCandidata        := substring(vCandidata, position('.' in vCandidata) + 1, 100);
	 	ELSE
	    	vEsquemaCandidato := 'public';
		END IF;
	 	INSERT INTO vistas_usadas VALUES (vEsquemaCandidato, vCandidata);
	 END IF;
	 
	 vCandidata := vProximoFrom;
 
  END LOOP;      

  vCandidata := vSql;
  LOOP 
     IF position('JOIN' IN vCandidata) = 0 THEN EXIT; END IF;
	 
	 vProximoFrom := substring(vCandidata, POSITION('JOIN' IN vCandidata)+5,length(vCandidata));
	 vCandidata   := vProximoFrom;
	 vProximoToken := length(vCandidata);
	 if position(' ' IN vCandidata) > 0 AND position(' ' IN vCandidata) < vProximoToken THEN  
	    vProximoToken := position(' ' IN vCandidata);
	 END IF;
	 if position(';' IN vCandidata) > 0 AND position(';' IN vCandidata) < vProximoToken THEN  
	    vProximoToken := position(';' IN vCandidata);
	 END IF;

	 vCandidata := substring(vCandidata,1, vProximoToken - 1);
	 if vCandidata ILIKE 'SELECT%' THEN vCandidata := ''; END IF;

	 IF vCandidata <> '' THEN 
		IF position('.' in vCandidata) > 0 THEN 
			vEsquemaCandidato := substring(vCandidata, 1, position('.' in vCandidata) - 1);
	    	vCandidata        := substring(vCandidata, position('.' in vCandidata) + 1, 100);
	 	ELSE
	    	vEsquemaCandidato := 'public';
	 	END IF;
	 
	 	INSERT INTO vistas_usadas VALUES (vEsquemaCandidato, vCandidata);
	 END IF;
	 
	 vCandidata := vProximoFrom;
 
  END LOOP;

  RETURN QUERY EXECUTE '(SELECT * FROM vistas_usadas)';

END;
$BODY$;

ALTER FUNCTION abaco_utiles.aavistaspreprocesar(character varying, character varying)
    OWNER TO abacouser;



CREATE OR REPLACE FUNCTION abaco_utiles.aavistasrestaurar(
	)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
  cursor_vistas REFCURSOR;
  vEsquema varchar;
  vVista   varchar;
  vSQL     varchar;
  
BEGIN
  
  PERFORM (SELECT abaco_utiles.aaVistasGenerarDependientes());
  PERFORM (SELECT abaco_utiles.aaVistasOrdenar());

  OPEN cursor_vistas FOR
       SELECT esquema, vista, sql
       FROM abaco_utiles.vistas 
       ORDER BY orden, esquema, vista;

  FETCH cursor_vistas INTO vEsquema, vVista, vSQL;
  LOOP
    EXIT WHEN (NOT FOUND);
    vSQL = replace(vSQL,'postgres','abacouser');
    EXECUTE 'CREATE OR REPLACE VIEW ' || vEsquema || '.' || vVista || ' AS ' || vSQL;
    FETCH cursor_vistas INTO vEsquema, vVista, vSQL;
  END LOOP;
  CLOSE cursor_vistas;
  
  RETURN True;
END;
$BODY$;

ALTER FUNCTION abaco_utiles.aavistasrestaurar()
    OWNER TO abacouser;

/* Ale
*/

select abaco_utiles.aaVistasDropear();

ALTER TABLE pad ALTER COLUMN email TYPE varchar(2000);
ALTER TABLE pad ALTER COLUMN mailsitio TYPE varchar(300);

select abaco_utiles.aaVistasRestaurar();

-- ====================================
SELECT aaVersion('485.00', '484.00');
-- ====================================
